﻿using System;
using System.Collections.Generic;
using System.Text;
using PPAP.Model;
using System.Data.OracleClient;
using System.Data;

namespace PPAP.DAO
{
    public class ContractPersonDao
    {
        public ContractPersonDao() { }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(ContractPerson model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "CONTACTPERSON");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CONTACTPERSON(");
            strSql.Append("NAME,GENDER,EMAIL,TELEPHONE,POSTITION,STATUS,PLANTID,ID,PASSWORD,FIRSTNAME,isuse,ISMANAGER)");
            strSql.Append(" values (");
            strSql.Append(":NAME,:GENDER,:EMAIL,:TELEPHONE,:POSTITION,:STATUS,:PLANTID,:ID,:PASSWORD,:FIRSTNAME,:isuse,:ISMANAGER)");
            OracleParameter[] parameters = {
					new OracleParameter(":NAME", OracleType.VarChar,50),
					new OracleParameter(":GENDER", OracleType.Char,20),
					new OracleParameter(":EMAIL", OracleType.VarChar,50),
					new OracleParameter(":TELEPHONE", OracleType.VarChar,50),
					new OracleParameter(":POSTITION", OracleType.VarChar,50),
					new OracleParameter(":STATUS", OracleType.VarChar,50),
					new OracleParameter(":PLANTID", OracleType.Number,4),
					new OracleParameter(":ID", OracleType.Number,4),
                                           new OracleParameter(":PASSWORD",OracleType.VarChar,50),
                                           new OracleParameter(":FIRSTNAME",OracleType.VarChar,50),
                                           new OracleParameter(":isuse",OracleType.Number,4),
                                           new OracleParameter(":ISMANAGER",OracleType.Number,4)};
            parameters[0].Value = model.NAME;
            parameters[1].Value = model.GENDER;
            parameters[2].Value = model.EMAIL.ToLower();
            parameters[3].Value = model.TELEPHONE;
            parameters[4].Value = model.POSTITION;
            parameters[5].Value = model.STATUS;
            parameters[6].Value = model.Plantid;
            parameters[7].Value = model.ID;
            parameters[8].Value = model.Password;
            parameters[9].Value = model.Firstname;
            parameters[10].Value = model.Isuse;
            parameters[11].Value = model.Ismanager;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(ContractPerson model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CONTACTPERSON set ");
            strSql.Append("NAME=:NAME,");
            strSql.Append("GENDER=:GENDER,");
            strSql.Append("EMAIL=:EMAIL,");
            strSql.Append("TELEPHONE=:TELEPHONE,");
            strSql.Append("POSTITION=:POSTITION,");
            strSql.Append("STATUS=:STATUS,");
            strSql.Append("PLANTID=:PLANTID,");
            strSql.Append("PASSWORD=:PASSWORD,");
            strSql.Append("ID=:ID,FIRSTNAME=:FIRSTNAME,isuse=:isuse,ISMANAGER=:ISMANAGER");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":NAME", OracleType.VarChar,50),
					new OracleParameter(":GENDER", OracleType.Char,20),
					new OracleParameter(":EMAIL", OracleType.VarChar,50),
					new OracleParameter(":TELEPHONE", OracleType.VarChar,50),
					new OracleParameter(":POSTITION", OracleType.VarChar,50),
					new OracleParameter(":STATUS", OracleType.VarChar,50),
					new OracleParameter(":PLANTID", OracleType.Number,4),
					new OracleParameter(":ID", OracleType.Number,4),
                                           new OracleParameter(":PASSWORD",OracleType.VarChar,50),
                                           new OracleParameter(":FIRSTNAME",OracleType.VarChar,50),
                                           new OracleParameter(":isuse",OracleType.Number,4),
                                           new OracleParameter(":ISMANAGER",OracleType.Number,4)};
            parameters[0].Value = model.NAME;
            parameters[1].Value = model.GENDER;
            parameters[2].Value = model.EMAIL.ToLower();
            parameters[3].Value = model.TELEPHONE;
            parameters[4].Value = model.POSTITION;
            parameters[5].Value = model.STATUS;
            parameters[6].Value = model.Plantid;
            parameters[7].Value = model.ID;
            parameters[8].Value = model.Password;
            parameters[9].Value = model.Firstname;
            parameters[10].Value = model.Isuse;
            parameters[11].Value = model.Ismanager;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CONTACTPERSON ");
            strSql.Append(" where isuse=1 and id="+id);
            OracleParameter[] parameters = {
};

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ContractPerson GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select NAME,GENDER,EMAIL,TELEPHONE,POSTITION,STATUS,PLANTID,ID,PASSWORD,Firstname,ISMANAGER,isuse from CONTACTPERSON  ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

            ContractPerson model = new ContractPerson();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.NAME = ds.Tables[0].Rows[0]["NAME"].ToString();
                model.GENDER = ds.Tables[0].Rows[0]["GENDER"].ToString();
                model.EMAIL = ds.Tables[0].Rows[0]["EMAIL"].ToString();
                model.TELEPHONE = ds.Tables[0].Rows[0]["TELEPHONE"].ToString();
                model.POSTITION = ds.Tables[0].Rows[0]["POSTITION"].ToString();
                model.STATUS = ds.Tables[0].Rows[0]["STATUS"].ToString();
                if (ds.Tables[0].Rows[0]["PLANTID"].ToString() != "")
                {
                    model.Plantid = int.Parse(ds.Tables[0].Rows[0]["PLANTID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.Password = ds.Tables[0].Rows[0]["PASSWORD"].ToString();
                model.Firstname = ds.Tables[0].Rows[0]["Firstname"].ToString();
                model.Ismanager = int.Parse(ds.Tables[0].Rows[0]["ISMANAGER"].ToString());
                if (ds.Tables[0].Rows[0]["isuse"].ToString() != "")
                {
                    model.Isuse = int.Parse(ds.Tables[0].Rows[0]["isuse"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体 根据用户名
        /// </summary>
        public ContractPerson GetModelByEmail(string Email)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select NAME,GENDER,EMAIL,TELEPHONE,POSTITION,STATUS,PLANTID,ID,PASSWORD,Firstname,ISMANAGER,isuse from CONTACTPERSON  ");
            strSql.Append(" where EMAIL='"+Email+"' ");
            OracleParameter[] parameters = {
};

            ContractPerson model = new ContractPerson();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.NAME = ds.Tables[0].Rows[0]["NAME"].ToString();
                model.GENDER = ds.Tables[0].Rows[0]["GENDER"].ToString();
                model.EMAIL = ds.Tables[0].Rows[0]["EMAIL"].ToString();
                model.TELEPHONE = ds.Tables[0].Rows[0]["TELEPHONE"].ToString();
                model.POSTITION = ds.Tables[0].Rows[0]["POSTITION"].ToString();
                model.STATUS = ds.Tables[0].Rows[0]["STATUS"].ToString();
                if (ds.Tables[0].Rows[0]["PLANTID"].ToString() != "")
                {
                    model.Plantid = int.Parse(ds.Tables[0].Rows[0]["PLANTID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.Password = ds.Tables[0].Rows[0]["PASSWORD"].ToString();
                model.Firstname = ds.Tables[0].Rows[0]["Firstname"].ToString();
                model.Ismanager = int.Parse(ds.Tables[0].Rows[0]["ISMANAGER"].ToString());
                if (ds.Tables[0].Rows[0]["isuse"].ToString() != "")
                {
                    model.Isuse = int.Parse(ds.Tables[0].Rows[0]["isuse"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select NAME,GENDER,EMAIL,TELEPHONE,POSTITION,STATUS,PLANTID,ID,rownum,PASSWORD,Firstname,isuse ");
            strSql.Append(" FROM CONTACTPERSON ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }
    }
}
